﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JKF.DataModule.IDAL;
using System.Data;
using JKF.Substructure;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace JKF.DataModule.SqlServerDAL
{
   /// <summary>
	/// 数据访问类dalDIC_LEVEL。
	/// </summary>
    public partial class dalDIC_LEVEL : IdalDIC_LEVEL
    {

        // 定义要获取哪些字段
        private const string FieldShow = "LEVEL_CODE, NAME, MEMO, DISPLAY_ORDER, USER_CREATE, DATE_CREATE, DATE_UPDATE,(case   when POINT_DISCOUNT is null then '' else convert(varchar(16),POINT_DISCOUNT) end) AS POINT_DISCOUNT,  (case   when PRODUCT_DISCOUNT is null then '' else convert(varchar(16),PRODUCT_DISCOUNT) end) AS  PRODUCT_DISCOUNT";
        private const string FieldOrder = "LEVEL_CODE, NAME";
        private const string FieldKey = "LEVEL_CODE";
        /// <summary>
        /// 获取会员级别列表
        /// </summary>
        /// <param name="NAME">会员级别名称</param>
        /// <param name="pageCurrent">当前页码</param>
        /// <param name="pageSize">一页显示多少</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="rowCount">总记录数</param>
        /// <returns>返回DataTable数据</returns>
        public static DataTable GetDic_LevelsDAL(string NAME, int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            var sql = new StringBuilder(@"  1=1 ");
            // 下面组装查询条件
            if (!string.IsNullOrEmpty(NAME))
            {
                sql.AppendFormat(" AND NAME  LIKE '%{0}%'", NAME);
            } 
            // 下面调用此方法去查询数据库
            return DataPage.GetPageInfo("dbo.DIC_LEVEL", FieldKey, pageCurrent, pageSize, FieldShow, FieldOrder, sql.ToString(), ref pageCount, ref rowCount).Tables[0];

        }

        /// <summary>
        /// 从数据库中获取店面编码，保证不重复,此编号返回的是四位编码，bll层应该转成2位
        /// </summary>
        /// <returns></returns>
        public static string GetLevelCodeDAL()
        {
            object obj = DB.HR.ExecuteScalar(CommandType.Text, @"SELECT TOP 1 CODE FROM [DIC_SHOP_CODE] WHERE CODE NOT IN (SELECT '00'+LEVEL_CODE FROM DIC_LEVEL)");
            return Convert.ToString(obj);
        }

        /// <summary>
        /// 判断是否存在此名称,true表示存在
        /// </summary>
        /// <param name="shop_name">店面名称</param>
        /// <returns></returns>
        public static bool ExistsLevelNameDAL(string level_name)
        {
            object obj = DB.HR.ExecuteScalar(CommandType.Text, "select count(1) from dbo.DIC_LEVEL  where NAME='" + level_name + "'");
            return Convert.ToInt32(obj) > 0;
        }
        /// <summary>
        /// 事务删除级别
        /// </summary>
        /// <param name="level_code"></param>
        /// <returns></returns>
        public static string TransactionSQLDeleteDAL(string level_code)
        {
            int result = -1;
            Database db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("[dbo].[proc_LEVELDelete]");
            db.AddInParameter(dbCommand, "@level_code", DbType.String, level_code);
            // db.AddInParameter(dbCommand, "@user_key", DbType.Guid, user_key);
            db.AddOutParameter(dbCommand, "@result", DbType.Int32, result);
            db.ExecuteNonQuery(dbCommand);
            return dbCommand.Parameters["@result"].Value.ToString();
        }
    }
}
